# 数据分析题目解答(建议先赞后看，养成习惯 如果不赞，先拉出去枪毙两分钟 作者：小匠IT)
import os
import pandas as pd
import matplotlib.pyplot as plt
from openpyxl import load_workbook
from openpyxl.chart import LineChart, Reference
import matplotlib.font_manager as fm

# 定义字体路径
font_path = 'fonts/SIMSUN.TTC'
prop = fm.FontProperties(fname=font_path)

# 设置matplotlib的中文字体
plt.rcParams['font.family'] = prop.get_name()
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号

# 定义输入和输出文件路径
input_file_path = 'data/市场需求趋势分析-原始数据.xlsx'
output_dir = 'output'

# 确保输出目录存在
if not os.path.exists(output_dir):
    os.makedirs(output_dir)

output_file_path = os.path.join(output_dir, '市场需求趋势分析-结果.xlsx')

# 读取Excel文件中的数据
df = pd.read_excel(input_file_path)

# 创建数据透视表
pivot_table = pd.pivot_table(df, values='交易指数', index=['日期'], columns=['类目名'], aggfunc="sum", fill_value=0)

# 将数据透视表转换为DataFrame（如果需要进一步操作）
pivot_df = pivot_table.reset_index()

# 检查输出文件是否已存在，如果存在则先删除
if os.path.exists(output_file_path):
    os.remove(output_file_path)

# 将数据透视表写入新的Excel文件或在现有文件中添加新工作表
with pd.ExcelWriter(output_file_path, engine='openpyxl', mode='w') as writer:
    pivot_df.to_excel(writer, sheet_name='PivotTable', index=False)

# 绘制牛肉卷/片的折线图并保存为PNG图片
plt.figure(figsize=(10, 6))
ax = pivot_df.plot(x='日期', y='牛肉卷/片', kind='line')
ax.set_title('2021年牛肉卷/片月度交易指数', fontproperties=prop)
ax.set_xlabel('月份', fontproperties=prop)
ax.set_ylabel('交易指数', fontproperties=prop)

# 确保所有文本都使用指定的字体
for label in ax.get_xticklabels() + ax.get_yticklabels():
    label.set_fontproperties(prop)

plt.tight_layout()
chart_image_path = os.path.join(output_dir, 'beef_roll_line_chart.png')
plt.savefig(chart_image_path)
plt.close()  # 关闭图形以释放内存

# 加载Excel文件以插入图表
wb = load_workbook(output_file_path)
ws = wb['PivotTable']

# 在Excel中创建LineChart对象
chart = LineChart()
data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=len(pivot_df)+1)  # 假设'牛肉卷/片'在第二列
cats = Reference(ws, min_col=1, min_row=2, max_row=len(pivot_df)+1)  # 假设'日期'在第一列
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.title = "2021年牛肉卷/片月度交易指数"
chart.x_axis.title = "月份"
chart.y_axis.title = "交易指数"

# 将图表添加到工作表
ws.add_chart(chart, "E2")  # E2 是放置图表的左上角单元格

# 保存工作簿
wb.save(output_file_path)

print(f"数据处理完成，数据透视表和图表已成功插入到 {output_file_path} 文件中。")